/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package AccesoDatos;

import Entidades.Monto;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

/**
 *
 * @author gaby
 */
public class ADMonto {

   
    private static LinkedList<Monto> Lista() throws ClassNotFoundException, SQLException {

        LinkedList<Monto> Lista = new LinkedList<>();
        String sql = "select * from monto";
        try (Connection cn = ConexionBD.Cadena();
                PreparedStatement ps = cn.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Lista.add(new Monto(rs.getInt(1), rs.getString(2), rs.getFloat(3)));
                }
            }
        }
        return Lista;
    }

    // Lista de montos pendientes
    private static LinkedList<Monto> Lista2(int idmatricula) throws ClassNotFoundException, SQLException {

        LinkedList<Monto> Lista = new LinkedList<>();
        
        /////////////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////////////
        //// TO-DO: MODIFCAR ESTA CONSULTA, CAMBIAR EN LA DB LA RELACION ID MATRICULA CON ID FACTURA
        String sql = "select * from monto where codigo not in(select mon.codigo\n"
                + "from monto mon inner join factura fac on fac.id=mon.codigo\n"
                + "inner join pagos p on p.idpago=fac.idpago\n"
                + "inner join matriculas ma on m.idmatricula=p.idmatricula where m.idmatricula=?)";
        try (Connection cn = ConexionBD.Cadena();
                PreparedStatement ps = cn.prepareStatement(sql)) {
            ps.setInt(1, idmatricula);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Lista.add(new Monto(rs.getInt(1), rs.getString(2), rs.getFloat(3)));
                }
            }
        }
        return Lista;
    }

    public static LinkedList<Monto> ListaMontos() throws ClassNotFoundException, SQLException {
        return Lista();
    }

    public static LinkedList<Monto> ListaMontosPendientes(int idmatricula) throws ClassNotFoundException, SQLException {
        return Lista2(idmatricula);
    }
}
